package com.kaishengit.dao;

import java.sql.ResultSet;
import java.sql.SQLException;

import com.kaishengit.entity.Employee;
import com.kaishengit.entity.EmployeeProject;
import com.kaishengit.entity.Project;
import com.kaishengit.util.DBHelp;
import com.kaishengit.util.RowMapper;

public class EmployeeProjectDao {
	private DBHelp<EmployeeProject> db = new DBHelp<EmployeeProject>();

	public boolean save(EmployeeProject ep) {
		String sql = "insert into t_employee_project(employeeid,projectid,role) values(?,?,?)";
		return db.executeSQL(sql, ep.getEmployeeid(),ep.getProjectid(),ep.getRole());
	}

	public EmployeeProject findById(String id,String projectid) {
		StringBuilder sql = new StringBuilder();
		sql.append("SELECT employeeid,te.username,te.password,te.pic,te.email,");
		sql.append("projectid,tp.name,tp.desc,tp.createtime,role FROM t_employee AS te ");
		sql.append("LEFT JOIN t_employee_project AS tep ON te.id = tep.employeeid ");
		sql.append("LEFT JOIN t_project AS tp ON tep.projectid = tp.id ");
		sql.append("WHERE employeeid = ? AND projectid = ?");
		return db.executeQueryForObject(sql.toString(), new EmployeeProjectRowMapper(), id,projectid);
	}
	private class EmployeeProjectRowMapper implements RowMapper<EmployeeProject>{

		public EmployeeProject mapRow(ResultSet rs) throws SQLException {
			EmployeeProject ep = new EmployeeProject();
			ep.setEmployeeid(rs.getString("employeeid"));
			
			Employee e = new Employee();
			e.setId(rs.getString("employeeid"));
			e.setEmail(rs.getString("email"));
			e.setPassword(rs.getString("password"));
			e.setPic(rs.getString("pic"));
			e.setUsername(rs.getString("username"));
			ep.setEmployee(e);
			
			Project p = new Project();
			p.setCreatetime(rs.getString("createtime"));
			p.setDesc(rs.getString("desc"));
			p.setId(rs.getString("projectid"));
			p.setName(rs.getString("name"));
			
			ep.setProject(p);
			ep.setProjectid(p.getId());
			ep.setRole(rs.getString("role"));
			return ep;
		}
		
	}	
	
}
